from django import forms
from django.contrib.auth.forms import AuthenticationForm
from .models import Server, Task, DatabaseConnection,QueryHistory
from crispy_forms.helper import FormHelper
from crispy_forms.layout import Submit, Layout, Field, Row, Column, HTML

from django.core.exceptions import ValidationError
import paramiko
import cx_Oracle
import pymysql
import pyodbc
class CustomLoginForm(AuthenticationForm):
    """自定义登录表单"""
    username = forms.CharField(
        widget=forms.TextInput(attrs={'class': 'form-control', 'placeholder': '用户名'}),
        label='用户名'
    )
    password = forms.CharField(
        widget=forms.PasswordInput(attrs={'class': 'form-control', 'placeholder': '密码'}),
        label='密码'
    )
    
    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)
        self.helper = FormHelper()
        self.helper.layout = Layout(
            Field('username'),
            Field('password'),
            HTML('<div class="d-grid gap-2"><button type="submit" class="btn btn-primary">登录</button></div>')
        )
        self.helper.form_show_labels = False


class ServerForm(forms.ModelForm):
    """服务器表单"""
    class Meta:
        model = Server
        fields = ['name', 'hostname', 'port', 'username', 'password', 'private_key']
        widgets = {
            'password': forms.PasswordInput(render_value=True),
            'private_key': forms.Textarea(attrs={'rows': 5}),
        }
    
    def __init__(self, *args, **kwargs):
        self.user = kwargs.pop('user', None)
        super().__init__(*args, **kwargs)
        self.helper = FormHelper()
        self.helper.form_method = 'post'
        self.helper.layout = Layout(
            Row(
                Column('name', css_class='col-md-6 mb-3'),
                Column('hostname', css_class='col-md-6 mb-3'),
            ),
            Row(
                Column('port', css_class='col-md-6 mb-3'),
                Column('username', css_class='col-md-6 mb-3'),
            ),
            Row(
                Column('password', css_class='col-md-12 mb-3'),
            ),
            Row(
                Column('private_key', css_class='col-md-12 mb-3'),
            ),
            HTML('<div class="d-flex justify-content-between">'),
            Submit('submit', '保存', css_class='btn btn-primary'),
            HTML('<a href="{% url \'server_list\' %}" class="btn btn-secondary">取消</a>'),
            HTML('</div>')
        )
    
    def save(self, commit=True):
        instance = super().save(commit=False)
        if self.user and not instance.created_by:
            instance.created_by = self.user
        if commit:
            instance.save()
        return instance


class TaskForm(forms.ModelForm):
    """任务表单"""
    class Meta:
        model = Task
        fields = ['name', 'description', 'server', 'command', 'status', 'schedule_time', 'is_enabled']
        widgets = {
            'description': forms.Textarea(attrs={'rows': 3}),
            'command': forms.Textarea(attrs={'rows': 5}),
            'schedule_time': forms.DateTimeInput(attrs={'type': 'datetime-local'}, format='%Y-%m-%dT%H:%M'),
        }
    
    def __init__(self, *args, **kwargs):
        self.user = kwargs.pop('user', None)
        super().__init__(*args, **kwargs)
        self.helper = FormHelper()
        self.helper.form_method = 'post'
        self.helper.layout = Layout(
            Row(
                Column('name', css_class='col-md-12 mb-3'),
            ),
            Row(
                Column('description', css_class='col-md-12 mb-3'),
            ),
            Row(
                Column('server', css_class='col-md-6 mb-3'),
                Column('status', css_class='col-md-6 mb-3'),
            ),
            Row(
                Column('schedule_time', css_class='col-md-6 mb-3'),
                Column('is_enabled', css_class='col-md-6 mb-3'),
            ),
            Row(
                Column('command', css_class='col-md-12 mb-3'),
            ),
            HTML('<div class="d-flex justify-content-between">'),
            Submit('submit', '保存', css_class='btn btn-primary'),
            HTML('<a href="{% url \'task_list\' %}" class="btn btn-secondary">取消</a>'),
            HTML('</div>')
        )
    
    def save(self, commit=True):
        instance = super().save(commit=False)
        if self.user and not instance.created_by:
            instance.created_by = self.user
        if commit:
            instance.save()
        return instance

        
class DatabaseConnectionForm(forms.ModelForm):
    test_connection = forms.BooleanField(required=False, widget=forms.HiddenInput())
    
    class Meta:
        model = DatabaseConnection
        fields = ['name', 'db_type', 'host', 'port', 'instance_name', 'username', 'password', 'database_name', 'description', 'is_active']
        widgets = {
            'password': forms.PasswordInput(),
            'description': forms.Textarea(attrs={'rows': 3}),
        }
    
    def clean(self):
        cleaned_data = super().clean()
        db_type = cleaned_data.get('db_type')
        host = cleaned_data.get('host')
        port = cleaned_data.get('port')
        username = cleaned_data.get('username')
        password = cleaned_data.get('password')
        database_name = cleaned_data.get('database_name')
        instance_name = cleaned_data.get('instance_name')
        test_connection = cleaned_data.get('test_connection', False)
        
        # 只有明确要求测试连接时才进行连接测试
        if test_connection and host and port and username and password and database_name:
            try:
                if db_type == 'mysql':
                    # 测试MySQL连接
                    conn = pymysql.connect(
                        host=host,
                        port=port,
                        user=username,
                        password=password,
                        db=database_name,
                        charset='utf8mb4',
                        connect_timeout=10
                    )
                    conn.close()
                    
                elif db_type == 'sqlserver':
                    # 测试SQL Server连接
                    conn_str = f"DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={host},{port};"
                    if instance_name:
                        conn_str += f"INSTANCE={instance_name};"
                    conn_str += f"DATABASE={database_name};UID={username};PWD={password}"
                    
                    conn = pyodbc.connect(conn_str, timeout=10)
                    conn.close()
                    
                elif db_type == 'oracle':
                    # 测试Oracle连接
                    dsn = cx_Oracle.makedsn(host, port, service_name=instance_name)
                    conn = cx_Oracle.connect(user=username, password=password, dsn=dsn, encoding="UTF-8", nencoding="UTF-8")
                    conn.close()
                    
            except Exception as e:
                raise ValidationError(f"数据库连接测试失败: {str(e)}")
        
        return cleaned_data

class SQLQueryForm(forms.Form):

    # 查看所有记录（包括is_active=False的）
    all_conns = DatabaseConnection.objects.all()
    print("所有连接记录数：", all_conns.count())  # 若为0，说明没数据
    # 查看is_active=True的记录
    active_conns = DatabaseConnection.objects.filter(is_active=True)
    print("活跃连接记录数：", active_conns.count())  # 若为0，说明没有活跃记录
    # 打印活跃记录详情（确认是否存在）
    for conn in active_conns:
        print(f"连接名：{conn.name}, 状态：{conn.is_active}")


    db_connection = forms.ModelChoiceField(
        queryset=DatabaseConnection.objects.filter(is_active=True),
        label="数据库连接",
        widget=forms.Select(attrs={'class': 'form-select'})
    )
    print("活跃连接记录数2：",db_connection.queryset.count())
    sql = forms.CharField(
        widget=forms.Textarea(attrs={'rows': 10, 'class': 'sql-editor form-control'}),
        label="SQL语句",
        required=False  # 移除原生required
    )
    page_size = forms.IntegerField(
        initial=50,
        min_value=10,
        max_value=500,
        label="每页行数",
        widget=forms.NumberInput(attrs={'class': 'form-control'})
    )

class StoredProcedureAnalysisForm(forms.Form):
    db_connection = forms.ModelChoiceField(
        queryset=DatabaseConnection.objects.filter(is_active=True, db_type='oracle'),
        label="Oracle数据库连接",        
        widget=forms.Select(attrs={'class': 'form-select'})
    )
    stored_procedure = forms.CharField(
        widget=forms.Textarea(attrs={'rows': 15, 'class': 'plsql-editor form-control'}),
        label="存储过程代码",
        required=False  # 移除原生required,
    )

